﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Security.Claims;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using Entity;
using IRepository;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.DependencyInjection;
using Newtonsoft.Json; // 新增：用于序列化日志参数

namespace Repository
{
    public class WarehouseRepository : IWarehouseRepository
    {
        private readonly DbConnection _dbConnection;
        private readonly ILogger<WarehouseRepository> _logger;
        private readonly IHttpContextAccessor _httpContextAccessor;
        private readonly IServiceProvider _serviceProvider;

        public WarehouseRepository(
            DbConnection dbConnection,
            ILogger<WarehouseRepository> logger,
            IHttpContextAccessor httpContextAccessor,
            IServiceProvider serviceProvider)
        {
            _dbConnection = dbConnection;
            _logger = logger;
            _httpContextAccessor = httpContextAccessor;
            _serviceProvider = serviceProvider;
        }

        public async Task AddAsync(WarehouseAddDTO dto)
        {
            var currentUser = GetCurrentUserName();
            _logger.LogInformation("当前登录用户：{UserName}", currentUser);

            // 新增：打印完整请求参数（调试用）
            _logger.LogDebug("【仓库新增】接收完整参数：{WarehouseAddDTO}", JsonConvert.SerializeObject(dto, Formatting.Indented));

            if (_dbConnection.State != ConnectionState.Open)
            {
                _logger.LogDebug("【仓库新增】数据库连接未打开，开始打开连接");
                await _dbConnection.OpenAsync();
            }

            using var transaction = await _dbConnection.BeginTransactionAsync();
            try
            {
                _logger.LogInformation("开始新增仓库+库位，仓库编号：{WarehouseNumber}，操作用户：{UserName}",
                    dto.WarehouseNumber, currentUser);

                // 步骤1：新增仓库（事务内执行）
                var warehouseId = await InsertWarehouse(dto, transaction);

                // 步骤2：新增“同时创建的库位”（核心修改：传递事务参数）
                if (dto.NewLocations != null && dto.NewLocations.Count > 0)
                {
                    var storageLocationRepo = _serviceProvider.GetRequiredService<IStorageLocationRepository>();
                    _logger.LogInformation("开始新增关联库位，数量：{LocationCount}，仓库ID：{WarehouseId}",
                        dto.NewLocations.Count, warehouseId);

                    foreach (var locDto in dto.NewLocations)
                    {
                        _logger.LogDebug("【库位新增】准备新增库位参数：{StorageLocationAddDTO}", JsonConvert.SerializeObject(locDto));
                        // 核心修改：传入事务，确保库位新增与仓库新增同事务
                        await storageLocationRepo.AddAsync(locDto, warehouseId, transaction);
                        _logger.LogInformation("新增库位成功（事务内），编号：{LocationNumber}，关联仓库ID：{WarehouseId}",
                            locDto.location_number, warehouseId);
                    }
                }

                // 步骤3：关联“已有库位”（原有逻辑保持不变）
                if (dto.SelectedLocationIds != null && dto.SelectedLocationIds.Count > 0)
                {
                    _logger.LogInformation("开始关联已有库位，数量：{Count}", dto.SelectedLocationIds.Count);

                    var updateSql = @"
                UPDATE wms_basic_storage_location 
                SET warehouse_id = @WarehouseId,
                    update_time = @UpdateTime,
                    update_by = @UpdateBy
                WHERE location_id IN @LocationIds AND is_delete = '0'";

                    var updateParams = new
                    {
                        WarehouseId = warehouseId,
                        LocationIds = dto.SelectedLocationIds,
                        UpdateTime = DateTime.Now,
                        UpdateBy = string.IsNullOrEmpty(currentUser) ? "system" : currentUser
                    };

                    _logger.LogDebug("【关联已有库位】执行SQL：{Sql}，参数：{Params}", updateSql, JsonConvert.SerializeObject(updateParams));
                    int affectedRows = await _dbConnection.ExecuteAsync(updateSql, updateParams, transaction);
                    _logger.LogInformation("已有库位关联完成，影响行数：{AffectedRows}", affectedRows);
                }
                else
                {
                    _logger.LogWarning("没有需要关联的已有库位");
                }

                await transaction.CommitAsync();
                _logger.LogInformation("事务提交成功，仓库ID：{WarehouseId}，新增库位{NewCount}个，关联库位{LinkCount}个",
                    warehouseId, dto.NewLocations?.Count ?? 0, dto.SelectedLocationIds?.Count ?? 0);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "新增仓库+库位失败，编号：{WarehouseNumber}，用户：{UserName}，异常详情：{ExceptionDetail}",
                    dto.WarehouseNumber, currentUser, ex.StackTrace);
                try
                {
                    await transaction.RollbackAsync();
                    _logger.LogInformation("事务已回滚");
                }
                catch (Exception rollbackEx)
                {
                    _logger.LogError(rollbackEx, "事务回滚失败，异常详情：{RollbackExceptionDetail}", rollbackEx.StackTrace);
                }
                throw new Exception($"新增失败: {ex.Message}", ex);
            }
        }

        // 私有方法：新增仓库（补充日志增强）
        private async Task<int> InsertWarehouse(WarehouseAddDTO dto, IDbTransaction transaction)
        {
            var insertSql = @"
        INSERT INTO wms_basic_warehouse 
        (warehouse_number, warehouse_name, warehouse_capacity, organizations_id, users_id, 
         warehouse_address, default_warehouse, sorting, warehouse_status, warehouse_locked, 
         note, create_by, create_time, update_by, update_time, is_delete)
        VALUES 
        (@warehouse_number, @warehouse_name, @warehouse_capacity, @organizations_id, @users_id, 
         @warehouse_address, @default_warehouse, @sorting, @warehouse_status, @warehouse_locked, 
         @note, @create_by, @create_time, @update_by, @update_time, @is_delete);
        SELECT CAST(SCOPE_IDENTITY() AS INT);";

            var warehouseParams = new
            {
                warehouse_number = dto.WarehouseNumber,
                warehouse_name = dto.WarehouseName,
                warehouse_capacity = dto.WarehouseCapacity,
                organizations_id = dto.OrgId,
                users_id = dto.ManagerId,
                warehouse_address = dto.Address ?? string.Empty,
                default_warehouse = dto.IsDefault ? "true" : "false",
                sorting = dto.Sorting.ToString(),
                warehouse_status = dto.Status.ToString(),
                warehouse_locked = dto.Locked.ToString(),
                note = dto.Note ?? string.Empty,
                create_by = GetCurrentUserName(),
                create_time = DateTime.Now,
                update_by = GetCurrentUserName(),
                update_time = DateTime.Now,
                is_delete = "0"
            };

            // 新增：打印插入SQL和参数（调试用）
            _logger.LogDebug("【仓库插入】执行SQL：{InsertSql}，参数：{WarehouseParams}",
                insertSql, JsonConvert.SerializeObject(warehouseParams));

            int warehouseId = await _dbConnection.QueryFirstOrDefaultAsync<int>(insertSql, warehouseParams, transaction);
            _logger.LogInformation("仓库插入成功，生成ID：{WarehouseId}", warehouseId);

            // 校验仓库ID是否有效
            if (warehouseId <= 0)
            {
                _logger.LogError("仓库插入失败，生成的ID无效：{WarehouseId}", warehouseId);
                throw new Exception("仓库新增失败，未生成有效仓库ID");
            }

            return warehouseId;
        }

        // 以下为原有方法（保持不变，仅补充必要日志）
        public async Task<WarehouseEntity> GetByNameAsync(string name)
        {
            _logger.LogDebug("【仓库查询】根据名称查询仓库：{WarehouseName}", name);
            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }

                var sql = "SELECT * FROM wms_basic_warehouse WHERE warehouse_name = @Name AND is_delete = 0";
                _logger.LogDebug("【仓库查询】执行SQL：{Sql}，参数：{Name}", sql, name);
                return await _dbConnection.QueryFirstOrDefaultAsync<WarehouseEntity>(sql, new { Name = name });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "根据名称查询仓库失败，名称：{Name}，异常详情：{ExceptionDetail}", name, ex.StackTrace);
                throw;
            }
        }

        public async Task UpdateAsync(WarehouseEntity entity)
        {
            _logger.LogInformation("【仓库更新】开始更新仓库，ID：{WarehouseId}", entity.warehouse_id);
            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }

                var currentUser = GetCurrentUserName();

                var sql = @"UPDATE wms_basic_warehouse SET
                                warehouse_number = @warehouse_number,
                                default_warehouse = @default_warehouse,
                                warehouse_name = @warehouse_name,
                                warehouse_capacity = @warehouse_capacity,
                                organizations_id = @organizations_id,
                                users_id = @users_id,
                                warehouse_address = @warehouse_address,
                                sorting = @sorting,
                                warehouse_status = @warehouse_status,
                                warehouse_locked = @warehouse_locked,
                                note = @note,
                                update_by = @update_by,
                                update_time = @update_time
                            WHERE warehouse_id = @warehouse_id";

                _logger.LogDebug("【仓库更新】执行SQL：{Sql}，参数：{Entity}", sql, JsonConvert.SerializeObject(entity));
                await _dbConnection.ExecuteAsync(sql, new
                {
                    entity.warehouse_number,
                    entity.default_warehouse,
                    entity.warehouse_name,
                    entity.warehouse_capacity,
                    entity.organizations_id,
                    entity.users_id,
                    entity.warehouse_address,
                    entity.sorting,
                    entity.warehouse_status,
                    entity.warehouse_locked,
                    entity.note,
                    update_by = currentUser,
                    update_time = DateTime.Now,
                    entity.warehouse_id
                });
                _logger.LogInformation("【仓库更新】成功，ID：{WarehouseId}", entity.warehouse_id);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "更新仓库失败，ID：{WarehouseId}，异常详情：{ExceptionDetail}", entity.warehouse_id, ex.StackTrace);
                throw;
            }
        }

        public async Task UpdateAsync(WarehouseEntity entity, IDbTransaction transaction)
        {
            _logger.LogInformation("【仓库更新-事务版】开始更新仓库，ID：{WarehouseId}", entity.warehouse_id);
            try
            {
                var currentUser = GetCurrentUserName();

                var sql = @"UPDATE wms_basic_warehouse SET
                                warehouse_number = @warehouse_number,
                                default_warehouse = @default_warehouse,
                                warehouse_name = @warehouse_name,
                                warehouse_capacity = @warehouse_capacity,
                                organizations_id = @organizations_id,
                                users_id = @users_id,
                                warehouse_address = @warehouse_address,
                                sorting = @sorting,
                                warehouse_status = @warehouse_status,
                                warehouse_locked = @warehouse_locked,
                                note = @note,
                                update_by = @update_by,
                                update_time = @update_time
                            WHERE warehouse_id = @warehouse_id";

                _logger.LogDebug("【仓库更新-事务版】执行SQL：{Sql}，参数：{Entity}", sql, JsonConvert.SerializeObject(entity));
                await _dbConnection.ExecuteAsync(sql, new
                {
                    entity.warehouse_number,
                    entity.default_warehouse,
                    entity.warehouse_name,
                    entity.warehouse_capacity,
                    entity.organizations_id,
                    entity.users_id,
                    entity.warehouse_address,
                    entity.sorting,
                    entity.warehouse_status,
                    entity.warehouse_locked,
                    entity.note,
                    update_by = currentUser,
                    update_time = DateTime.Now,
                    entity.warehouse_id
                }, transaction);
                _logger.LogInformation("【仓库更新-事务版】成功，ID：{WarehouseId}", entity.warehouse_id);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "在事务中更新仓库失败，ID：{WarehouseId}，异常详情：{ExceptionDetail}", entity.warehouse_id, ex.StackTrace);
                throw;
            }
        }

        public async Task<PaginationResult<WarehouseEntity>> GetPageListAsync(
            string warehouseName,
            int? warehouseStatus,
            int? organizationId,
            string warehouseManager,
            PaginationQuery pagination)
        {
            try
            {
                _logger.LogInformation("【Repository】开始查询仓库列表，参数：仓库名称={WarehouseName}，状态={WarehouseStatus}，部门ID={OrganizationId}，页码={PageIndex}，页大小={PageSize}",
                    warehouseName, warehouseStatus, organizationId, pagination.PageIndex, pagination.PageSize);

                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }

                var pageIndex = pagination.PageIndex;
                var pageSize = pagination.PageSize;
                var whereConditions = new List<string>();
                var parameters = new DynamicParameters();
                parameters.Add("@SkipCount", (pageIndex - 1) * pageSize);
                parameters.Add("@PageSize", pageSize);
                parameters.Add("@IsDelete", "0");

                if (!string.IsNullOrEmpty(warehouseName))
                {
                    whereConditions.Add("(warehouse_name LIKE @WarehouseName OR warehouse_number LIKE @WarehouseName)");
                    parameters.Add("@WarehouseName", $"%{warehouseName}%");
                }
                if (warehouseStatus.HasValue)
                {
                    whereConditions.Add("warehouse_status = @WarehouseStatus");
                    parameters.Add("@WarehouseStatus", warehouseStatus.Value.ToString());
                }
                if (organizationId.HasValue)
                {
                    whereConditions.Add("organizations_id = @OrganizationId");
                    parameters.Add("@OrganizationId", organizationId.Value);
                }
                if (!string.IsNullOrEmpty(warehouseManager))
                {
                    whereConditions.Add("users_id = @WarehouseManager");
                    parameters.Add("@WarehouseManager", warehouseManager);
                }
                whereConditions.Add("is_delete = @IsDelete");

                var whereSql = whereConditions.Any() ? "WHERE " + string.Join(" AND ", whereConditions) : "";

                var countSql = $"SELECT COUNT(1) FROM wms_basic_warehouse {whereSql}";
                var dataSql = $@"
                SELECT 
                    w.*,
                    (SELECT ISNULL(SUM(sl.storage_location_capacity), 0) FROM wms_basic_storage_location sl 
                     WHERE sl.warehouse_id = w.warehouse_id AND sl.is_delete = '0') AS LocationCount
                FROM wms_basic_warehouse w
                {whereSql}
                ORDER BY w.create_time DESC, w.sorting DESC
                OFFSET @SkipCount ROWS FETCH NEXT @PageSize ROWS ONLY
                ";

                _logger.LogDebug("【Repository】执行COUNT SQL: {CountSql}，参数：{Parameters}", countSql, JsonConvert.SerializeObject(parameters));

                var totalCount = await _dbConnection.QueryFirstOrDefaultAsync<int>(countSql, parameters);
                _logger.LogInformation("【Repository】查询到总数: {TotalCount}", totalCount);

                var items = await _dbConnection.QueryAsync<WarehouseEntity>(dataSql, parameters);
                var itemList = items?.ToList() ?? new List<WarehouseEntity>();

                _logger.LogInformation("【Repository】查询到数据条数: {ItemCount}", itemList.Count);
                if (itemList.Any())
                {
                    var first = itemList[0];
                    _logger.LogDebug("【Repository】第一条数据: {FirstItem}", JsonConvert.SerializeObject(first));
                }

                return new PaginationResult<WarehouseEntity>
                {
                    Data = itemList,
                    TotalCount = totalCount,
                    PageIndex = pageIndex,
                    PageSize = pageSize
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "【Repository】查询异常，异常详情：{ExceptionDetail}", ex.StackTrace);
                throw;
            }
        }

        private string GetCurrentUserName()
        {
            if (_httpContextAccessor?.HttpContext == null)
            {
                _logger.LogInformation("【Repository】HttpContext为空，使用默认用户: system");
                return "system";
            }

            try
            {
                var session = _httpContextAccessor.HttpContext.Session;
                if (session != null)
                {
                    byte[] userBytes;
                    if (session.TryGetValue("LoginUserName", out userBytes))
                    {
                        var userName = Encoding.UTF8.GetString(userBytes);
                        if (!string.IsNullOrEmpty(userName))
                        {
                            _logger.LogInformation("【Repository】从Session获取到用户: {UserName}", userName);
                            return userName;
                        }
                    }
                }

                if (_httpContextAccessor.HttpContext.User?.Identity?.IsAuthenticated == true)
                {
                    var identityName = _httpContextAccessor.HttpContext.User.Identity.Name;
                    if (!string.IsNullOrEmpty(identityName))
                    {
                        _logger.LogInformation("【Repository】从Identity获取到用户: {IdentityName}", identityName);
                        return identityName;
                    }
                }

                var claim = _httpContextAccessor.HttpContext.User?.FindFirst(ClaimTypes.Name);
                if (claim != null && !string.IsNullOrEmpty(claim.Value))
                {
                    _logger.LogInformation("【Repository】从Claims获取到用户: {ClaimValue}", claim.Value);
                    return claim.Value;
                }

                _logger.LogInformation("【Repository】未获取到用户信息，使用默认值: system");
                return "system";
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "【Repository】获取用户信息异常，异常详情：{ExceptionDetail}", ex.StackTrace);
                return "system";
            }
        }

        private readonly Dictionary<string, string> _statusMapping = new()
        {
            {"0", "停用"},
            {"1", "启用"},
            {"2", "锁定"}
        };

        public async Task<List<WarehouseStatusDTO>> GetWarehouseStatusListAsync()
        {
            _logger.LogInformation("【仓库状态查询】开始查询所有仓库状态");
            if (_dbConnection.State != ConnectionState.Open)
            {
                await _dbConnection.OpenAsync();
            }

            var sql = "SELECT DISTINCT warehouse_status FROM wms_basic_warehouse WHERE is_delete = '0'";
            _logger.LogDebug("【仓库状态查询】执行SQL：{Sql}", sql);
            var statusValues = (await _dbConnection.QueryAsync<string>(sql)).ToList();

            _logger.LogInformation("查询到的仓库状态值：{StatusValues}", string.Join(",", statusValues));

            return statusValues.Select(s => new WarehouseStatusDTO
            {
                StatusValue = s,
                StatusName = _statusMapping.TryGetValue(s, out var name) ? name : $"未知状态({s})"
            }).ToList();
        }

        public async Task DeleteAsync(int warehouseId)
        {
            _logger.LogInformation("开始删除仓库，ID：{WarehouseId}", warehouseId);
            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }

                var sql = @"
            UPDATE wms_basic_warehouse 
            SET is_delete = '1',
                update_by = @UpdateBy,
                update_time = @UpdateTime 
            WHERE warehouse_id = @WarehouseId AND is_delete = '0'";

                var currentUser = GetCurrentUserName();
                _logger.LogDebug("【仓库删除】执行SQL：{Sql}，参数：{WarehouseId}，操作用户：{UserName}",
                    sql, warehouseId, currentUser);
                var affectedRows = await _dbConnection.ExecuteAsync(sql, new
                {
                    WarehouseId = warehouseId,
                    UpdateBy = currentUser,
                    UpdateTime = DateTime.Now
                });

                if (affectedRows == 0)
                {
                    _logger.LogWarning("删除仓库失败，未找到ID为{WarehouseId}的有效仓库", warehouseId);
                    throw new KeyNotFoundException($"未找到ID为{warehouseId}的仓库或已被删除");
                }

                _logger.LogInformation("仓库删除成功，ID：{WarehouseId}", warehouseId);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "删除仓库异常，ID：{WarehouseId}，异常详情：{ExceptionDetail}", warehouseId, ex.StackTrace);
                throw;
            }
        }

        public async Task<int> BatchUpdateStatusAsync(List<int> warehouseIds, string status)
        {
            if (warehouseIds == null || !warehouseIds.Any())
            {
                _logger.LogWarning("批量更新仓库状态：仓库ID列表为空");
                return 0;
            }

            try
            {
                _logger.LogInformation("【批量更新仓库状态】开始更新，状态：{Status}，仓库IDs：{WarehouseIds}",
                    status, string.Join(",", warehouseIds));

                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }

                var currentUser = GetCurrentUserName();
                var sql = @"UPDATE wms_basic_warehouse 
                            SET warehouse_status = @Status,
                                update_by = @UpdateBy,
                                update_time = @UpdateTime
                            WHERE warehouse_id IN @WarehouseIds 
                              AND is_delete = '0'";

                var parameters = new
                {
                    Status = status,
                    WarehouseIds = warehouseIds,
                    UpdateBy = currentUser,
                    UpdateTime = DateTime.Now
                };

                _logger.LogDebug("【批量更新仓库状态】执行SQL：{Sql}，参数：{Parameters}", sql, JsonConvert.SerializeObject(parameters));
                int affectedRows = await _dbConnection.ExecuteAsync(sql, parameters);
                _logger.LogInformation("批量更新仓库状态成功，状态：{Status}，影响行数：{AffectedRows}，IDs：{Ids}",
                    status, affectedRows, string.Join(',', warehouseIds));
                return affectedRows;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "批量更新仓库状态失败，状态：{Status}，IDs：{Ids}，异常详情：{ExceptionDetail}",
                    status, string.Join(',', warehouseIds), ex.StackTrace);
                throw;
            }
        }

        public async Task<List<string>> CheckWarehousesHasStockAsync(List<int> warehouseIds)
        {
            if (warehouseIds == null || !warehouseIds.Any())
                return new List<string>();

            _logger.LogInformation("【仓库库存检查】开始检查仓库是否有库存，仓库IDs：{WarehouseIds}", string.Join(",", warehouseIds));
            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }

                var sql = @"SELECT DISTINCT w.warehouse_name 
                    FROM wms_basic_warehouse w
                    JOIN wms_knzy_inventory i ON w.warehouse_id = i.warehouse_id
                    WHERE w.warehouse_id IN @WarehouseIds 
                      AND w.is_delete = '0' 
                      AND i.current_inventory > 0";

                _logger.LogDebug("【仓库库存检查】执行SQL：{Sql}，参数：{WarehouseIds}", sql, string.Join(",", warehouseIds));
                return (await _dbConnection.QueryAsync<string>(sql, new { WarehouseIds = warehouseIds }))
                    .ToList();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "检查仓库库存失败，IDs：{Ids}，异常详情：{ExceptionDetail}", string.Join(',', warehouseIds), ex.StackTrace);
                throw;
            }
        }

        public async Task<List<WarehouseEntity>> GetByIdsAsync(List<int> ids)
        {
            if (ids == null || !ids.Any())
                return new List<WarehouseEntity>();

            _logger.LogInformation("【仓库批量查询】根据ID列表查询仓库，IDs：{WarehouseIds}", string.Join(",", ids));
            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }

                var sql = "SELECT * FROM wms_basic_warehouse WHERE warehouse_id IN @Ids AND is_delete = '0'";
                _logger.LogDebug("【仓库批量查询】执行SQL：{Sql}，参数：{Ids}", sql, string.Join(",", ids));
                return (await _dbConnection.QueryAsync<WarehouseEntity>(sql, new { Ids = ids })).AsList();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "根据ID集合查询仓库失败，IDs：{Ids}，异常详情：{ExceptionDetail}", string.Join(',', ids), ex.StackTrace);
                throw;
            }
        }

        public async Task<WarehouseEntity> GetByIdAsync(int warehouseId)
        {
            _logger.LogInformation("【仓库查询】根据ID查询仓库，ID：{WarehouseId}", warehouseId);
            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }

                var sql = @"
                    SELECT * 
                    FROM wms_basic_warehouse 
                    WHERE warehouse_id = @WarehouseId 
                      AND is_delete = '0'";

                _logger.LogDebug("【仓库查询】执行SQL：{Sql}，参数：{WarehouseId}", sql, warehouseId);
                return await _dbConnection.QueryFirstOrDefaultAsync<WarehouseEntity>(
                    sql,
                    new { WarehouseId = warehouseId }
                );
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "根据ID查询仓库失败，ID：{WarehouseId}，异常详情：{ExceptionDetail}", warehouseId, ex.StackTrace);
                throw;
            }
        }

        public async Task<int> GetLocationCountByWarehouseIdAsync(int warehouseId)
        {
            _logger.LogInformation("【库位数量查询】查询仓库关联的库位数量，仓库ID：{WarehouseId}", warehouseId);
            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }

                var sql = @"
                    SELECT COUNT(*) 
                    FROM wms_basic_storage_location 
                    WHERE warehouse_id = @WarehouseId 
                      AND is_delete = '0'";

                _logger.LogDebug("【库位数量查询】执行SQL：{Sql}，参数：{WarehouseId}", sql, warehouseId);
                return await _dbConnection.QueryFirstOrDefaultAsync<int>(
                    sql,
                    new { WarehouseId = warehouseId }
                );
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询仓库库位数量失败，仓库ID：{WarehouseId}，异常详情：{ExceptionDetail}", warehouseId, ex.StackTrace);
                throw;
            }
        }

        public async Task<bool> ExistsNumberAsync(string number, int excludeId)
        {
            _logger.LogInformation("【仓库编号校验】校验仓库编号是否存在，编号：{Number}，排除ID：{ExcludeId}", number, excludeId);
            if (_dbConnection.State != ConnectionState.Open)
                await _dbConnection.OpenAsync();

            var sql = @"
        SELECT COUNT(1) 
        FROM wms_basic_warehouse 
        WHERE warehouse_number = @Number 
          AND warehouse_id != @ExcludeId 
          AND is_delete = '0'";

            _logger.LogDebug("【仓库编号校验】执行SQL：{Sql}，参数：{Number}，{ExcludeId}", sql, number, excludeId);
            var count = await _dbConnection.QueryFirstOrDefaultAsync<int>(sql, new { Number = number, ExcludeId = excludeId });
            return count > 0;
        }

        public async Task UpdateWarehouseAsync(WarehouseEditDTO dto)
        {
            var currentUser = GetCurrentUserName();
            _logger.LogInformation("开始编辑仓库，ID：{WarehouseId}，操作用户：{UserName}", dto.WarehouseId, currentUser);

            if (_dbConnection.State != ConnectionState.Open)
                await _dbConnection.OpenAsync();

            using var transaction = await _dbConnection.BeginTransactionAsync();
            try
            {
                // 1. 更新仓库基本信息
                var updateWarehouseSql = @"
            UPDATE wms_basic_warehouse 
            SET warehouse_number = @warehouse_number,
                warehouse_name = @warehouse_name,
                warehouse_capacity = @warehouse_capacity,
                organizations_id = @organizations_id,
                users_id = @users_id,
                warehouse_address = @warehouse_address,
                default_warehouse = @default_warehouse,
                sorting = @sorting,
                warehouse_status = @warehouse_status,
                note = @note,
                update_by = @update_by,
                update_time = @update_time
            WHERE warehouse_id = @warehouse_id 
              AND is_delete = '0'";

                var warehouseParams = new
                {
                    warehouse_id = dto.WarehouseId,
                    warehouse_number = dto.WarehouseNumber,
                    warehouse_name = dto.WarehouseName,
                    warehouse_capacity = dto.WarehouseCapacity,
                    organizations_id = dto.OrgId,
                    users_id = dto.ManagerId,
                    warehouse_address = dto.Address ?? string.Empty,
                    default_warehouse = dto.IsDefault ? "true" : "false",
                    sorting = dto.Sorting,
                    warehouse_status = dto.Status.ToString(),
                    note = dto.Note ?? string.Empty,
                    update_by = currentUser,
                    update_time = DateTime.Now
                };

                _logger.LogDebug("【仓库编辑】执行更新SQL：{Sql}，参数：{Params}", updateWarehouseSql, JsonConvert.SerializeObject(warehouseParams));
                int warehouseAffected = await _dbConnection.ExecuteAsync(updateWarehouseSql, warehouseParams, transaction);
                if (warehouseAffected <= 0)
                    throw new KeyNotFoundException($"未找到ID为{dto.WarehouseId}的仓库或已被删除");

                // 2. 先解除原有库位关联（将原关联的库位warehouse_id设为null）
                var clearLocationSql = @"
            UPDATE wms_basic_storage_location 
            SET warehouse_id = NULL,
                update_by = @UpdateBy,
                update_time = @UpdateTime
            WHERE warehouse_id = @WarehouseId 
              AND is_delete = '0'";

                _logger.LogDebug("【仓库编辑】执行解除库位关联SQL：{Sql}，参数：{WarehouseId}", clearLocationSql, dto.WarehouseId);
                await _dbConnection.ExecuteAsync(clearLocationSql, new
                {
                    WarehouseId = dto.WarehouseId,
                    UpdateBy = currentUser,
                    UpdateTime = DateTime.Now
                }, transaction);

                // 3. 关联新选择的库位
                if (dto.SelectedLocationIds != null && dto.SelectedLocationIds.Count > 0)
                {
                    var updateLocationSql = @"
                UPDATE wms_basic_storage_location 
                SET warehouse_id = @WarehouseId,
                    update_by = @UpdateBy,
                    update_time = @UpdateTime
                WHERE location_id IN @LocationIds 
                  AND is_delete = '0'";

                    _logger.LogDebug("【仓库编辑】执行关联库位SQL：{Sql}，参数：{WarehouseId}，{LocationIds}",
                        updateLocationSql, dto.WarehouseId, string.Join(",", dto.SelectedLocationIds));
                    int locationAffected = await _dbConnection.ExecuteAsync(updateLocationSql, new
                    {
                        WarehouseId = dto.WarehouseId,
                        LocationIds = dto.SelectedLocationIds,
                        UpdateBy = currentUser,
                        UpdateTime = DateTime.Now
                    }, transaction);

                    _logger.LogInformation("仓库关联库位更新完成，影响行数：{LocationAffected}", locationAffected);
                }

                await transaction.CommitAsync();
                _logger.LogInformation("仓库编辑成功，ID：{WarehouseId}", dto.WarehouseId);
            }
            catch (Exception ex)
            {
                await transaction.RollbackAsync();
                _logger.LogError(ex, "编辑仓库失败，ID：{WarehouseId}，异常详情：{ExceptionDetail}", dto.WarehouseId, ex.StackTrace);
                throw new Exception($"编辑仓库失败：{ex.Message}", ex);
            }
        }

        public async Task<List<WarehouseEntity>> GetAllAsync()
        {
            _logger.LogInformation("【仓库查询】查询所有未删除的仓库");
            var sql = "SELECT * FROM wms_basic_warehouse WHERE is_delete = 0";
            _logger.LogDebug("【仓库查询】执行SQL：{Sql}", sql);
            var warehouses = await _dbConnection.QueryAsync<WarehouseEntity>(sql);
            return warehouses.AsList();
        }

        public async Task<List<StorageLocationEntity>> GetAllStorageLocationsAsync()
        {
            _logger.LogInformation("【库位查询】查询所有有效库位（未删除+启用）");
            var sql = @"SELECT location_id, location_name, location_number, warehouse_id
                FROM wms_basic_storage_location 
                WHERE is_delete = 0 AND location_status = 1
                ORDER BY sorting, location_name";

            _logger.LogDebug("【库位查询】执行SQL：{Sql}", sql);
            return (await _dbConnection.QueryAsync<StorageLocationEntity>(sql)).AsList();
        }

        public async Task<List<StorageLocationEntity>> GetStorageLocationsByWarehouseAsync(string warehouseName)
        {
            _logger.LogInformation("【库位查询】根据仓库名称查询库位，仓库名称：{WarehouseName}", warehouseName);

            // 先根据仓库名称获取仓库ID
            var warehouseSql = "SELECT warehouse_id FROM wms_basic_warehouse WHERE warehouse_name = @WarehouseName AND is_delete = 0";
            var warehouseId = await _dbConnection.QueryFirstOrDefaultAsync<int?>(warehouseSql, new { WarehouseName = warehouseName });

            if (warehouseId == null)
            {
                _logger.LogWarning("【库位查询】未找到仓库名称对应的仓库ID，仓库名称：{WarehouseName}", warehouseName);
                return new List<StorageLocationEntity>();
            }

            _logger.LogInformation("【库位查询】仓库名称：{WarehouseName} 对应的仓库ID：{WarehouseId}", warehouseName, warehouseId);

            // 根据仓库ID查询库位
            var sql = @"SELECT location_id, location_name, location_number, warehouse_id
        FROM wms_basic_storage_location
        WHERE warehouse_id = @WarehouseId 
          AND is_delete = 0 
          AND location_status = 1
        ORDER BY sorting, location_name";

            _logger.LogDebug("【库位查询】执行SQL：{Sql}，参数：{WarehouseId}", sql, warehouseId);
            return (await _dbConnection.QueryAsync<StorageLocationEntity>(sql, new { WarehouseId = warehouseId })).AsList();
        }

        public async Task<bool> TestStorageLocationConnection()
        {
            try
            {
                Console.WriteLine("🔄 开始测试库位表连接");
                var sql = "SELECT COUNT(*) FROM wms_basic_storage_location WHERE is_delete = 0";
                var count = await _dbConnection.ExecuteScalarAsync<int>(sql);
                Console.WriteLine($"✅ 库位表连接测试成功，记录数：{count}");
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ 库位表连接测试失败：{ex.Message}");
                _logger.LogError(ex, "库位表连接测试失败，异常详情：{ExceptionDetail}", ex.StackTrace);
                return false;
            }
        }

        public async Task<StorageLocationEntity> GetFirstValidStorageLocationAsync(int warehouseId)
        {
            _logger.LogInformation("【库位查询】查询仓库第一个有效库位，仓库ID：{WarehouseId}", warehouseId);
            var sql = @"
        SELECT TOP 1 * FROM wms_basic_storage_location 
        WHERE warehouse_id = @WarehouseId 
          AND location_status = '1' 
          AND is_delete = 0";

            _logger.LogDebug("【库位查询】执行SQL：{Sql}，参数：{WarehouseId}", sql, warehouseId);
            return await _dbConnection.QueryFirstOrDefaultAsync<StorageLocationEntity>(
                sql, new { WarehouseId = warehouseId });
        }

        public async Task<StorageLocationEntity> GetFirstValidStorageLocationAsync(int warehouseId, IDbTransaction transaction)
        {
            _logger.LogInformation("【库位查询-事务版】查询仓库第一个有效库位，仓库ID：{WarehouseId}", warehouseId);
            var sql = @"
        SELECT TOP 1 * FROM wms_basic_storage_location 
        WHERE warehouse_id = @WarehouseId 
          AND location_status = '1' 
          AND is_delete = 0";

            _logger.LogDebug("【库位查询-事务版】执行SQL：{Sql}，参数：{WarehouseId}", sql, warehouseId);
            return await _dbConnection.QueryFirstOrDefaultAsync<StorageLocationEntity>(
                sql,
                new { WarehouseId = warehouseId },
                transaction
            );
        }

        public async Task<WarehouseEntity> GetByIdAsync(int warehouseId, IDbTransaction transaction = null)
        {
            _logger.LogInformation("【仓库查询-事务版】根据ID查询仓库，ID：{WarehouseId}", warehouseId);
            var sql = "SELECT * FROM wms_basic_warehouse WHERE warehouse_id = @WarehouseId AND is_delete = '0'";
            _logger.LogDebug("【仓库查询-事务版】执行SQL：{Sql}，参数：{WarehouseId}", sql, warehouseId);
            return await _dbConnection.QueryFirstOrDefaultAsync<WarehouseEntity>(
                sql,
                new { WarehouseId = warehouseId },
                transaction
            );
        }

        public async Task<WarehouseEntity> GetByNameAsync(string name, IDbTransaction transaction)
        {
            _logger.LogInformation("【仓库查询-事务版】根据名称查询仓库，名称：{WarehouseName}", name);
            try
            {
                var sql = "SELECT * FROM wms_basic_warehouse WHERE warehouse_name = @Name AND is_delete = 0";
                _logger.LogDebug("【仓库查询-事务版】执行SQL：{Sql}，参数：{Name}", sql, name);
                return await _dbConnection.QueryFirstOrDefaultAsync<WarehouseEntity>(
                    sql,
                    new { Name = name },
                    transaction
                );
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "在事务中根据名称查询仓库失败，名称：{Name}，异常详情：{ExceptionDetail}", name, ex.StackTrace);
                throw;
            }
        }

        public async Task<int?> GetValidLocationIdByWarehouseIdAsync(int warehouseId, IDbTransaction transaction = null)
        {
            _logger.LogInformation("【有效库位ID查询】查询仓库第一个有效库位ID，仓库ID：{WarehouseId}", warehouseId);
            var sql = @"
                SELECT TOP 1 location_id 
                FROM wms_basic_storage_location 
                WHERE warehouse_id = @WarehouseId 
                  AND is_delete = '0' 
                  AND location_status = '1' 
                ORDER BY sorting ASC";

            _logger.LogDebug("【有效库位ID查询】执行SQL：{Sql}，参数：{WarehouseId}", sql, warehouseId);
            return await _dbConnection.QueryFirstOrDefaultAsync<int?>(
                sql,
                new { WarehouseId = warehouseId },
                transaction
            );
        }

        public async Task<string> GetLocationNameByLocationIdAsync(int locationId, IDbTransaction transaction = null)
        {
            _logger.LogInformation("【库位名称查询】根据库位ID查询名称，库位ID：{LocationId}", locationId);
            var sql = "SELECT location_name FROM wms_basic_storage_location WHERE location_id = @LocationId AND is_delete = 0";
            _logger.LogDebug("【库位名称查询】执行SQL：{Sql}，参数：{LocationId}", sql, locationId);
            return await _dbConnection.QueryFirstOrDefaultAsync<string>(
                sql,
                new { LocationId = locationId },
                transaction
            ) ?? "未知库位";
        }
    }
}